﻿/* Copyright (c) 2011-2012, Zetatron Consulting
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

* Neither the name of Zetatron Consulting nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using AionCxxLibrary.Utilities;
using AionCxxLibrary.Models;
using AionCxxLibrary.Enums;
using WorldServer.Models;

namespace WorldServer.DAO
{
    public class ItemDAO
    {
        /// <summary>
        /// Loads equipment templates for character list.
        /// </summary>
        /// <param name="characterId"></param>
        /// <returns></returns>
        public static List<TItem> SelectEquipmentsTemplatesByCharacterId(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT dbo.Items.TemplateID, dbo.Items.Color, dbo.Items.SkinID FROM dbo.Items, dbo.Storages WHERE dbo.Storages.CharacterID = @CharID  AND dbo.Storages.ItemID = dbo.Items.ID AND dbo.Storages.StorageID = 0 AND dbo.Items.Equipped = 1", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            List<TItem> result = new List<TItem>();
            Log.Query(cmd);
            try
            {
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    TItem item = new TItem();
                    item.ItemID = rdr.GetInt32(0);
                    item.ItemColor = rdr.GetInt32(1);
                    int skin = rdr.GetInt32(2);
                    if (skin > 0)
                        item.ItemID = skin;
                    result.Add(item);
                }
            }
            catch (Exception e)
            {
                Log.Error("Cannot load equipment templates for " + characterId + "!", e);
                result.Clear();
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        public static Item SelectItemByInternalID(long itemInternalId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT ID, TemplateID, Count, Color, Crafter, CreationTime, TemporaryTradeTime, SoulBound, EnchantLevel, SkinID, FusionedItem, OptionalSocketFlag, Equipped FROM dbo.Items WHERE ID = @ID", con);
            cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = itemInternalId;
            // add command params here
            Log.Query(cmd);
            Item item = null;
            try
            {
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    item = new Item(rdr.GetInt64(0), rdr.GetInt32(1));
                    item.Count = rdr.GetInt64(2);
                    item.Color = rdr.GetInt32(3);
                    item.Crafter = rdr.GetString(4);
                    item.CreationTime = rdr.GetInt64(5);
                    item.TemporaryTradeTimeLeft = rdr.GetInt32(6);
                    item.SoulBound = rdr["SoulBound"].ToString() == "1";
                    item.EnchantLevel = rdr.GetByte(8);
                    item.SkinID = rdr.GetInt32(9);
                    item.FusionedItem = rdr.GetInt64(10) == 0 ? null : SelectItemByInternalID(rdr.GetInt32(10));
                    item.OptionalSocketFlag = rdr.GetByte(11);
                    item.Equipped = rdr["Equipped"].ToString() == "1";
                }
            }
            catch (Exception e)
            {
                Log.Error("", e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return item;
        }

        public static List<Item> SelectStorageByCharacterId(int characterId, byte storageId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT dbo.Items.ID, dbo.Storages.Slot FROM dbo.Items, dbo.Storages WHERE dbo.Storages.CharacterID = @CharID AND dbo.Storages.ItemID = dbo.Items.ID and dbo.Storages.StorageID = @StorageID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            cmd.Parameters.Add("@StorageID", SqlDbType.TinyInt).Value = storageId;
            List<Item> result = new List<Item>();
            Log.Query(cmd);
            try
            {
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Item item = SelectItemByInternalID(rdr.GetInt64(0));
                    int slot = rdr.GetInt32(1);
                    item.Slot = slot;
                    result.Add(item);
                }
            }
            catch (Exception e)
            {
                Log.Error("Cannot load storage " + storageId + " for character " + characterId + "!", e);
                result.Clear();
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        public static void DeleteFromStorage(long internalId, byte storageId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("DELETE FROM dbo.Storages WHERE ItemID = @ItemID AND StorageID = @StorageID", con);
            cmd.Parameters.Add("@ItemID", SqlDbType.BigInt).Value = internalId;
            cmd.Parameters.Add("@StorageID", SqlDbType.TinyInt).Value = storageId;
            // add command params here
            Log.Query(cmd);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot delete item " + internalId + " from storage " + storageId, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static void InsertToStorage(long internalId, int characterId, byte storageId, int slot)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("INSERT INTO dbo.Storages(StorageID, CharacterID, ItemID, Slot) VALUES(@StorageID, @CharID, @ItemID, @Slot)", con);
            cmd.Parameters.Add("@StorageID", SqlDbType.TinyInt).Value = storageId;
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            cmd.Parameters.Add("@ItemID", SqlDbType.BigInt).Value = internalId;
            cmd.Parameters.Add("@Slot", SqlDbType.Int).Value = slot;
            SqlTransaction transac = null;
            Log.Query(cmd);
            try
            {
                con.Open();
                transac = con.BeginTransaction();
                cmd.Transaction = transac;
                cmd.ExecuteNonQuery();
                transac.Commit();
            }
            catch (Exception e)
            {
                Log.Error("Cannot insert item " + internalId + " in slot " + slot + " of storage " + storageId + " of character " + characterId, e);
                if(transac != null) transac.Rollback();
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static void UpdateItemSlot(long internalId, int slot)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("UPDATE dbo.Storages SET Slot = @Slot WHERE ItemID = @ItemID", con);
            cmd.Parameters.Add("@ItemID", SqlDbType.BigInt).Value = internalId;
            cmd.Parameters.Add("@Slot", SqlDbType.Int).Value = slot;
            // add command params here
            Log.Query(cmd);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot update item slot: " + internalId + " to slot " + slot, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static long InsertItem(int charId, int slot, int storage, int templateId, long count, int color, string crafter, int temporaryTradeTimeLeft, bool soulBound, byte enchantLevel, int skinId, byte optionalSocketFlag, bool equipped, bool insertToStorage)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("INSERT INTO dbo.Items(TemplateID, Count, Color, Crafter, CreationTime, TemporaryTradeTime, SoulBound, EnchantLevel, SkinID, FusionedItem, OptionalSocketFlag, Equipped) " +
                "VALUES(@TemplateID, @Count, @Color, @Crafter, @CreationTime, @TemporaryTradeTime, @SoulBound, @EnchantLevel, @SkinID, @FusionedItem, @OptionalSocketFlag, @Equipped); SELECT @@IDENTITY;", con);
            cmd.Parameters.Add("@TemplateID", SqlDbType.Int).Value = templateId;
            cmd.Parameters.Add("@Count", SqlDbType.BigInt).Value = count;
            cmd.Parameters.Add("@Color", SqlDbType.Int).Value = color;
            cmd.Parameters.Add("@Crafter", SqlDbType.VarChar).Value = crafter;
            cmd.Parameters.Add("@CreationTime", SqlDbType.BigInt).Value = Utility.DateTimeToTimestamp(DateTime.Now);
            cmd.Parameters.Add("@TemporaryTradeTime", SqlDbType.TinyInt).Value = temporaryTradeTimeLeft;
            cmd.Parameters.Add("@SoulBound", SqlDbType.TinyInt).Value = soulBound ? 1 : 0;
            cmd.Parameters.Add("@EnchantLevel", SqlDbType.TinyInt).Value = enchantLevel;
            cmd.Parameters.Add("@SkinID", SqlDbType.Int).Value = skinId;
            cmd.Parameters.Add("@FusionedItem", SqlDbType.BigInt).Value = 0;
            cmd.Parameters.Add("@OptionalSocketFlag", SqlDbType.TinyInt).Value = optionalSocketFlag;
            cmd.Parameters.Add("@Equipped", SqlDbType.TinyInt).Value = equipped ? 1 : 0;
            SqlCommand cmd2 = new SqlCommand("INSERT INTO dbo.Storages(StorageID, CharacterID, ItemID, Slot) VALUES(@StorageID, @CharID, @ItemID, @Slot)", con);
            cmd2.Parameters.Add("@StorageID", SqlDbType.TinyInt).Value = storage;
            cmd2.Parameters.Add("@CharID", SqlDbType.Int).Value = charId;
            cmd2.Parameters.Add("@Slot", SqlDbType.Int).Value = slot;
            long result = 0;
            SqlTransaction transac = null;
            try
            {
                con.Open();
                transac = con.BeginTransaction();
                cmd.Transaction = transac;
                cmd2.Transaction = transac;
                Log.Query(cmd);
                object res = cmd.ExecuteScalar();
                if (res != null)
                {
                    result = long.Parse(res.ToString());

                    if (insertToStorage)
                    {
                        cmd2.Parameters.Add("@ItemID", SqlDbType.BigInt).Value = result;
                        Log.Query(cmd2);
                        cmd2.ExecuteNonQuery();
                    }

                    transac.Commit();
                }
                else
                    transac.Rollback();
            }
            catch (Exception e)
            {
                Log.Error("Cannot create item " + templateId + "/" + count + "/enchant=" + enchantLevel + " !", e);
                if (transac != null)
                    transac.Rollback();
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        public static bool UpdateItemCount(long internalId, long itemCount)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("UPDATE dbo.Items SET Count = @Count WHERE ID = @IntID", con);
            cmd.Parameters.Add("@Count", SqlDbType.BigInt).Value = itemCount;
            cmd.Parameters.Add("@IntID", SqlDbType.BigInt).Value = internalId;
            bool result = false;
            SqlTransaction transaction = null;
            Log.Query(cmd);
            try
            {
                con.Open();
                transaction = con.BeginTransaction();
                cmd.Transaction = transaction;
                cmd.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (Exception e)
            {
                Log.Error("Cannot update item count " + internalId + " new count " + itemCount + "!", e);
                result = false;
                if(transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        public static byte SelectCubeSize(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT CubeSize FROM dbo.StorageSizes WHERE CharacterID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            byte result = 0;
            Log.Query(cmd);
            try
            {
                con.Open();
                object res = cmd.ExecuteScalar();
                if (res == null)
                {
                    UpdateStorageSizes(characterId, 27, 27);
                    result = 27;
                }
                else
                    result = byte.Parse(res.ToString());
            }
            catch (Exception e)
            {
                Log.Error("Cannot get cube size for character " + characterId, e);
                result = 29;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        public static void UpdateStorageSizes(int characterId, short cubeSize, short warehouseSize)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("DELETE FROM dbo.StorageSizes WHERE CharacterID = @CharID; INSERT INTO dbo.StorageSizes(CharacterID, CubeSize, WarehouseSize) VALUES(@CharID, @CSize, @WSize)", con);
            cmd.Parameters.Add("@CSize", SqlDbType.TinyInt).Value = cubeSize;
            cmd.Parameters.Add("@WSize", SqlDbType.TinyInt).Value = warehouseSize;
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            // add command params here
            Log.Query(cmd);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot update storage sizes to " + cubeSize + "/" + warehouseSize + " for character " + characterId, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static byte SelectCharacterWarehouseSize(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT WarehouseSize FROM dbo.StorageSizes WHERE CharacterID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            byte result = 0;
            Log.Query(cmd);
            try
            {
                con.Open();
                object res = cmd.ExecuteScalar();
                if (res == null)
                {
                    UpdateStorageSizes(characterId, 27, 27);
                    result = 27;
                }
                else
                    result = byte.Parse(res.ToString());
            }
            catch (Exception e)
            {
                Log.Error("Cannot get warehouse size for character " + characterId, e);
                result = 0;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        public static void UpdateEquipped(long internalId, bool equipped)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("UPDATE dbo.Items SET Equipped = @Equipped WHERE ID = @IntID", con);
            cmd.Parameters.Add("@Equipped", SqlDbType.TinyInt).Value = equipped ? 1 : 0;
            cmd.Parameters.Add("@IntID", SqlDbType.BigInt).Value = internalId;
            Log.Query(cmd);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot set " + (equipped ? "" : "un") + "equipped item " + internalId, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static void DeleteItems(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("DELETE FROM dbo.Items WHERE ID IN(SELECT ItemID FROM dbo.Storages WHERE CharacterID = @CharID);", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            Log.Query(cmd);
            // add command params here
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot delete items for " + characterId, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static void UpdateItemSoulBound(long internalId, bool bound)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("UPDATE dbo.Items SET SoulBound = @Sb WHERE ID = @ID", con);
            cmd.Parameters.Add("@Sb", SqlDbType.TinyInt).Value = bound ? 1 : 0;
            cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = internalId;
            Log.Query(cmd);
            // add command params here
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot set item " + internalId + " as " + (bound ? "" : "not ") + " soulbound!", e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

    }
}
